﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using GovernmentOfficeProject.LevelDataAccessModel;
using GovernmentOfficeProject.SysTools;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace GovernmentOfficeProject.LevelDataAccess
{
    public class DAL_USER_Contacts
    {
        /// <summary>
        /// 获得联系人分页列表
        /// </summary>
        /// <param name="contacts">查询条件</param>
        /// <param name="pageSize">显示条数</param>
        /// <param name="currentPage">当前页数</param>
        /// <returns></returns>
        public List<Entity_USER_Contacts> getContactsListPagination(Entity_USER_Contacts contacts, int pageSize, int currentPage)
        {
            string sql = @"SELECT TOP {0} * FROM PER_CONTACTS WHERE (ID NOT IN (SELECT TOP {1} id FROM PER_CONTACTS WHERE 1=1 {2} ORDER BY id)) {3} ORDER BY ID"
                    .FormatWith(pageSize, (currentPage - 1) * pageSize, getConditionSql(contacts), getConditionSql(contacts));
            List<Entity_USER_Contacts> list = getContactsListBySql(sql);
            //AppLog.Write(sql, AppLog.LogMessageType.Debug);
            return list;
        }

        /// <summary>
        /// 获得所有联系人
        /// </summary>
        /// <param name="contacts">联系人对象 ，可做条件查询</param>
        /// <returns></returns>
        public List<Entity_USER_Contacts> getContactsList(Entity_USER_Contacts contacts)
        {
            string sql = @"SELECT  * FROM PER_CONTACTS WHERE 1=1 {0}".FormatWith(this.getConditionSql(contacts));
            List<Entity_USER_Contacts> list = getContactsListBySql(sql);
            return list;
        }

        /// <summary>
        /// 获得数据总数
        /// </summary>
        /// <returns></returns>
        public int getAllCount(Entity_USER_Contacts contacts)
        {
            string sql = "select count(*) COUNT from PER_CONTACTS where 1=1 {0}".FormatWith(this.getConditionSql(contacts));
            return Convert.ToInt16(DAL_DBGM.GetDataSet(sql).Tables[0].Rows[0]["COUNT"]);
        }


        /// <summary>
        /// 保存联系人
        /// </summary>
        /// <param name="contacts"></param>
        /// <returns></returns>
        public bool saveContacts(Entity_USER_Contacts contacts)
        {
            string sql = @"insert into PER_CONTACTS (USER_ID,NAME,COMPANY,MOBILE_NUMBER,PHONE_NUMBER,FAX_NUMBER,EMAIL,ADDRESS,REMARK) values({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')"
                .FormatWith(contacts.UserId,      
                            contacts.Name,        
                            contacts.Company,     
                            contacts.MobileNumber,
                            contacts.PhoneNumber, 
                            contacts.FaxNumber,   
                            contacts.Email,       
                            contacts.Address,     
                            contacts.Remark       
                            );
            return DAL_DBGM.InsertInfo(sql.ToString());
        }


       /// <summary>
        /// 更新联系人
       /// </summary>
       /// <param name="contacts"></param>
       /// <returns></returns>
        public bool updateContactsById(Entity_USER_Contacts contacts)
        {
            string sql = "update PER_CONTACTS set NAME = '{0}',COMPANY = '{1}',MOBILE_NUMBER = '{2}',PHONE_NUMBER = '{3}',FAX_NUMBER = '{4}',EMAIL = '{5}',ADDRESS = '{6}',REMARK  = '{7}' where id = {8}"
                .FormatWith(contacts.Name,      
                            contacts.Company,     
                            contacts.MobileNumber,
                            contacts.PhoneNumber, 
                            contacts.FaxNumber,   
                            contacts.Email,       
                            contacts.Address,     
                            contacts.Remark,
                            contacts.Id
                            );

            return DAL_DBGM.UpdateInfo(sql);
        }


        /// <summary>
        /// 删除联系人
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool deleteContctsById(decimal id)
        {
            string sql = @"delete PER_CONTACTS where id = {0}".FormatWith(id);
            return DAL_DBGM.DeleteInfo(sql);
        }


       
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private List<Entity_USER_Contacts> getContactsListBySql(string sql)
        {
            DataSet dst = DAL_DBGM.GetDataSet(sql);
            Entity_USER_Contacts contacts = null;
            List<Entity_USER_Contacts> contactsList = new List<Entity_USER_Contacts>();
            foreach (DataRow row in dst.Tables[0].Rows)
            {
                contacts = new Entity_USER_Contacts();
                contacts.Id = Convert.ToDecimal(row["ID"]);
                contacts.Name = Convert.ToString(row["Name"]);
                contacts.Company = Convert.ToString(row["Company"]);
                contacts.MobileNumber = Convert.ToString(row["Mobile_Number"]);
                contacts.PhoneNumber = Convert.ToString(row["Phone_Number"]);
                contacts.FaxNumber = Convert.ToString(row["Fax_Number"]);
                contacts.Address = Convert.ToString(row["Address"]);
                contacts.Remark = Convert.ToString(row["Remark"]);

                contactsList.Add(contacts);
            }
            return contactsList;
        }
        


        /// <summary>
        /// 获得条件
        /// </summary>
        /// <param name="contacts"></param>
        /// <returns></returns>
        private string getConditionSql(Entity_USER_Contacts contacts)
        {
            if (contacts == null) return "";
            StringBuilder whereSql = new StringBuilder();
            if (contacts.Id != 0)
            {
                whereSql.Append(" and ID <> ").Append(contacts.Id);
            }
            if(contacts.UserId != 0)
            {
                whereSql.Append(" and USER_ID = ").Append(contacts.UserId);
            }
            if (!contacts.Name.IsNullEmpty())
            {
                whereSql.Append(" and NAME like '%").Append(contacts.Name).Append("%'");
            }

            if (!contacts.MobileNumber.IsNullEmpty())
            {
                whereSql.Append(" and Mobile_Number = '").Append(contacts.MobileNumber).Append("'");
            }

            if (!contacts.PhoneNumber.IsNullEmpty())
            {
                whereSql.Append(" and Phone_Number = '").Append(contacts.PhoneNumber).Append("'");
            }

            if(!contacts.Company.IsNullEmpty())
            {
                whereSql.Append(" and Company like '%").Append(contacts.Company).Append("%'");
            }
            return whereSql.ToString();
        }
    }
}